import os
import sys
from pathlib import Path

# Add the project root to the Python path
sys.path.append(str(Path(__file__).resolve().parent.parent))

from database.db_connection import get_mysql_connection, close_mysql_connection
from backend.utils.logger import setup_logger

# Set up logger
logger = setup_logger('init_db')

def init_database(drop_existing=False):
    """Initialize the database with the schema from schema.sql
    
    Args:
        drop_existing (bool): If True, drop all existing tables before initialization.
                             If False, only create tables that don't exist yet.
    """
    logger.info("Initializing database...")
    
    conn = None
    cursor = None
    try:
        # Read the schema file
        schema_path = os.path.join(os.path.dirname(__file__), 'schema.sql')
        with open(schema_path, 'r') as f:
            schema = f.read()
        
        # Connect to the database
        conn = get_mysql_connection()
        cursor = conn.cursor()
        
        if drop_existing:
            # Drop existing tables if requested
            logger.info("Dropping all existing tables...")
            cursor.execute("SET FOREIGN_KEY_CHECKS = 0")
            
            # Get all tables
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            
            # Drop each table
            for table in tables:
                table_name = table[0]
                logger.info(f"Dropping table: {table_name}")
                cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
            
            cursor.execute("SET FOREIGN_KEY_CHECKS = 1")
        
        # Split the schema into individual statements
        statements = schema.split(';')
        
        # Execute each statement
        for statement in statements:
            statement = statement.strip()
            if statement:
                # Only create tables that don't exist yet
                if statement.upper().startswith("CREATE TABLE"):
                    # Add IF NOT EXISTS to avoid errors if table already exists
                    if "IF NOT EXISTS" not in statement.upper():
                        statement = statement.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS", 1)
                    logger.info(f"Creating table (if not exists): {statement[0:50]}...")
                cursor.execute(statement)
        
        conn.commit()
        logger.info("Database initialized successfully")
        return True
    except Exception as e:
        if conn:
            conn.rollback()
        logger.error(f"Error initializing database: {e}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            close_mysql_connection(conn)

if __name__ == "__main__":
    if init_database():
        print("Database initialization complete!")
    else:
        print("Database initialization failed. Check logs for details.") 